# coding: utf-8

# NOTE by zhouhl 2018-04-12
# 抢单策略调整
# 1.铜牌的派单点转化为汇币，1个派单点转化为40个汇币
# 2.原铜牌会员赠送3个月铜牌会费套餐
# 3.原新订单提醒次数转化为天数:
#   3.1 顺延月数 = 提醒次数 / (10 * 31)，向上取整
#   3.2 顺延月数 < 3，直接顺延3月
#   3.3 顺延月数 > 3，按照顺延月数执行即可

import os
import datetime
import math
import set_env_path

from sqlalchemy import create_engine
from sqlalchemy.sql import text

engine = create_engine(
    os.getenv('DATABASE_URL')
    or 'mysql+pymysql://root:@127.0.0.1/wph_www_prod?charset=utf8'
)
conn = engine.connect()


def dispatch_point_to_bid_point():
    """
    派单点转化为汇币
    """
    # step1 查询有派单点的用户 pack.dispatch_point
    # step2 这些用户的派单点清零，增加相应汇币 pack.bid_point ++
    # step3 写一条 BidCostRecord 记录此次行为 bid_cost_record
    step1_sql = text("""
        select
            id, uid, dispatch_point, bid_point
        from
            pack
        where
            dispatch_point > 0;
    """)

    step2_sql = text("""
        update
            pack
        set
            dispatch_point = 0,
            bid_point = bid_point + :exchange_bid_point
        where
            id = :pack_id;
    """)

    # BidCostRecord 记录备注，cost_type 固定为12
    step3_sql = text("""
        insert into
            bid_cost_record
        (
            user_id, cost_type, bid_point, left_bid_point,
            created_at, updated_at
        ) values (
            :uid, 12, :exchange_bid_point, :left_bid_point,
            UNIX_TIMESTAMP(), UNIX_TIMESTAMP()
        );
    """)

    have_dispatch_point_packs = conn.execute(step1_sql).fetchall()

    for i in have_dispatch_point_packs:
        uid = i.uid
        pack_id = i.id
        dispatch_point = i.dispatch_point
        bid_point = i.bid_point
        # 这几个是内部用户，派单点不做转换
        if str(uid) in ('3152484', '3365335', '3356783'):
            exchange_bid_point = 0
        else:
            exchange_bid_point = dispatch_point * 40
        left_bid_point = bid_point + exchange_bid_point
        print('*' * 50)
        print(
            uid, pack_id, dispatch_point,
            bid_point, exchange_bid_point, left_bid_point)
        print('*' * 50)

        conn.execute(
            step2_sql,
            exchange_bid_point = exchange_bid_point,
            pack_id = pack_id,
        )

        conn.execute(
            step3_sql,
            uid = uid,
            exchange_bid_point = exchange_bid_point,
            left_bid_point = left_bid_point,
        )


def three_months_bronze_medal():
    """
    原铜牌会员，赠送 93天 铜牌会费
    """
    # user.level == 20，pack.bronze_medal_end_date += 31 * 3
    bronze_medal_user_sql = text("""
        select
            id
        from
            user
        where
            level = 20;
    """)

    set_three_months_bronze_medal_sql = text("""
        update
            pack
        set
            bronze_medal_end_date = :bronze_medal_end_date
        where
            uid = :uid;
    """)

    bronze_medal_users = conn.execute(bronze_medal_user_sql)

    bronze_medal_end_date = datetime.date.today() + datetime.timedelta(days=93)
    for i in bronze_medal_users:
        uid = i.id
        conn.execute(
            set_three_months_bronze_medal_sql,
            bronze_medal_end_date = bronze_medal_end_date,
            uid = uid
        )


def sms_point_to_sms_end_date():
    """
    原新订单提醒次数转化为天数
    """
    # pack.sms_point 表示 系统赠送 + 管理员赠送 + 用户购买 的短信提醒
    # pack.paid_sms_point 表示 用户购买 的短信提醒
    get_all_pack_sql = text("""
        select id, sms_point, sms_end_date from pack;
    """)

    update_pack_sql = text("""
        update
            pack
        set
            sms_point = 0,
            paid_sms_point = 0,
            sms_end_date = :sms_end_date
        where
            id = :pack_id;
    """)

    packs = conn.execute(get_all_pack_sql)

    for pack in packs:
        pack_id = pack.id
        before_update_sms_end_date = pack.sms_end_date

        before_sms_point = pack.sms_point
        exchange_sms_months = math.ceil(before_sms_point / (10 * 31))
        if exchange_sms_months < 3:
            exchange_sms_months = 3
        exchange_sms_days = 31 * exchange_sms_months

        if (before_update_sms_end_date
            and before_update_sms_end_date > datetime.date.today()
            ):
            base_sms_end_date = before_update_sms_end_date
        else:
            base_sms_end_date = datetime.date.today()
        exchange_sms_end_date = base_sms_end_date \
            + datetime.timedelta(days = exchange_sms_days)

        conn.execute(
            update_pack_sql,
            sms_end_date = exchange_sms_end_date,
            pack_id = pack_id,
        )


if __name__ == '__main__':
    # dispatch_point_to_bid_point()
    # three_months_bronze_medal()
    # sms_point_to_sms_end_date()
    pass
